﻿#include "dboperation.h"
#include"QFile"
#include<QList>
#include"Beans/userinfobean.h"
#include"Beans/datainfobean.h"
#include<qdebug.h>
DBOperation::DBOperation(QObject *parent) : QObject(parent)
{
    SetTableName("QXZDMPA");
    dbOpenSF=opendatabase();
}

DBOperation::~DBOperation()
{
    db.close();
}


/******************************************************************************
**功能:
**输入参数:
**输出参数:
**作者:
**日期:
**参考:
*******************************************************************************/
void DBOperation::SetTableName(QString table)
{
    tableName = table + ".db";
}

/******************************************************************************
**功能:打开数据库
**输入参数:
**输出参数:
**作者:
**日期:2015/10/27
**参考:
*******************************************************************************/
bool DBOperation::opendatabase()
{
    if(QSqlDatabase::contains("qt_sql_default_connection"))
    {
        db = QSqlDatabase::database("qt_sql_default_connection");
    }
    else
    {
        db = QSqlDatabase::addDatabase("QSQLITE");
    }

    db.setDatabaseName(tableName);
    return db.open();
}
bool DBOperation::closedatabase()
{
    db.close();
    return true;
}
bool DBOperation::CheckDataBase(QString filePath)
{

    QFile file(filePath);
    if (!file.exists())
    {
        if(dbOpenSF){
            QSqlQuery query;
            query.exec("create table UserInfo(ID int primary key,userID varchar(50)  NOT NULL,pwd varchar(50),userName varchar(50) NOT NULL,Age varchar(50),Sex varchar(50),Height varchar(50),Weight varchar(50))");
            query.exec("create table DataInfo(ID int primary key,userID varchar(50) Not NULL,userName varchar(50),Age varchar(50),Sex varchar(50),Height varchar(50),Weight varchar(50),MeasureTime TimeStamp NOT NULL DEFAULT(datetime('now','localtime')),FilePath varchar(100))");
            return true;
        }else{
            return false;
        }
    }
}
bool DBOperation::QuertUserInfo(QString usName){
    if(dbOpenSF){
        QSqlQuery query;
        query.exec(QString("select * from UserInfo where userID ='%1'").arg(usName));
        if(query.next()){
            return true;
        }else{
            return false;
        }
    }else{
        return false;
    }

}
bool DBOperation::QuertUserInfo(QString usName,QString pwd){
    if(dbOpenSF){
        QSqlQuery query;
        query.exec(QString("select * from UserInfo where userID ='%1' AND pwd='%2'").arg(usName).arg(pwd));
        if(query.next()){
            return true;
        }else{
            return false;
        }
    }else{
        return false;
    }

}
UserInfoBean DBOperation::QueryUserInfoBysql(QString sql){
    UserInfoBean userInfoBean;
    if(dbOpenSF){
        QSqlQuery query;
        query.exec(sql);
        if(query.next()){
            userInfoBean.UserID=query.value(1).toString();
            userInfoBean.UserPwd=query.value(2).toString();
            userInfoBean.UserName=query.value(3).toString();
            userInfoBean.UserAge=query.value(4).toString();
            userInfoBean.UserSex=query.value(5).toString();
            userInfoBean.UserHeight=query.value(6).toString();
            userInfoBean.UserWeight=query.value(7).toString();

        }
    }
    return userInfoBean;

}
QList<UserInfoBean>  DBOperation::QuertAllUserInfo(){
    if(dbOpenSF){
        QList<UserInfoBean> res;
        QSqlQuery query;
        query.exec(QString("select * from UserInfo"));
        UserInfoBean userinfobean;
        while (query.next())
        {
            userinfobean.UserID=query.value(1).toString();
            userinfobean.UserPwd=query.value(2).toString();
            userinfobean.UserName=query.value(3).toString();
            userinfobean.UserAge=query.value(4).toString();
            userinfobean.UserSex=query.value(5).toString();
            userinfobean.UserHeight=query.value(6).toString();
            userinfobean.UserWeight=query.value(7).toString();
            res.append(userinfobean);

        }
        return res;
    }
}
QList<DataInfoBean>  DBOperation::QuertAllDataInfo(){
    if(dbOpenSF){
        QList<DataInfoBean> res;
        QSqlQuery query;
        query.exec(QString("select * from DataInfo"));
        DataInfoBean datainfobean;
        while (query.next())
        {
//            datainfobean.UserID=query.value(1).toString();
//            datainfobean.UserName=query.value(2).toString();
//            datainfobean.UserAge=query.value(3).toString();
//            datainfobean.UserSex=query.value(4).toString();
//            datainfobean.UserHeight=query.value(5).toString();
//            datainfobean.UserWeight=query.value(6).toString();
//            datainfobean.MeasureTime=query.value(7).toString();
//            datainfobean.Path=query.value(8).toString();
            res.append(datainfobean);

        }
        return res;
    }
}
/******************************************************************************
**功能:构造SQL插入语句.
**输入参数:
**输出参数:
**作者:whb
**修改:2019.1.4 传入参数地址类型，去掉
*******************************************************************************/
bool DBOperation::insert(QString table, QStringList names, QStringList values)
{
    if (names.size() != values.size())
    {
        return false;
    }

    QSqlQuery query(QSqlDatabase::database());

    QString sql = QString("insert into ") + table + QString("(");

    int i;

    for (i=0; i < names.size(); i++)
    {
        sql = sql + names.value(i);
        if (i != names.size() - 1)
        {
            sql+=QString(",");
        }
        else
        {

            sql = sql + QString(")");
        }
    }

    sql = sql + QString("values (");

    for (i = 0; i < values.size(); i++)
    {
        sql = sql + QString("'") + values.value(i) + QString("'");
        if (i != values.size()-1)
        {
                    sql = sql + QString(",");
                }
            }

            sql = sql + QString(")");

            if (query.exec(sql))
            {
                return true;
            }
            else
            {
                return false;
            }
        }
/******************************************************************************
**功能:构造SQL修改语句.修改
**输入参数:
**输出参数:
**作者:
**日期:
**参考:
*******************************************************************************/
bool DBOperation::Updata(QString table, QStringList names, QStringList values, QString expression)
{
    if (names.size() != values.size())
    {
        return false;
    }

    //UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值
    QSqlQuery query(QSqlDatabase::database());
    QString sql = QString("update ")+table+QString(" set ");
    for (int i = 0; i < names.size(); i++)
    {
        sql = sql + names.at(i);
        sql = sql + QString(" = '");
        sql = sql + values.at(i);
        sql = sql + QString("'");
        if (i != names.size()-1)
        {
            sql = sql + QString(" ,");
        }
    }

    sql = sql + QString(" where ") + expression;
    if (query.exec(sql))
    {
        return true;
    }
    else
    {
        return false;
    }
}
 /******************************************************************************
**功能:构造SQL删除语句.
**输入参数:
**输出参数:
**作者:whb
**修改:2019.1.4 入参去掉地址
*******************************************************************************/
bool DBOperation::del(QString table, QString expression)
{
    //DELETE FROM 表名称 WHERE 列名称 = 值
    QSqlQuery query(QSqlDatabase::database());
    QString sql = QString("delete from ") + table + QString(" where ") + expression;
    if (query.exec(sql))
    {
        return true;
    }
    else
    {
        return false;
    }
}
void DBOperation::GetValues(QString &table, QStringList &values)
{
    QSqlQuery query(QSqlDatabase::database());
    QString sql = QString("select * from ") + table;
    query.exec(sql);
    while (query.next())
    {
        values << query.value(0).toString();
    }
}
QList<DataInfoBean> DBOperation::QueryDataInfoBy(QString expression)
{
    if(dbOpenSF){
        QList<DataInfoBean> res;
        QSqlQuery query;
        QString sql=QString("select * from DataInfo where ") + expression;
        qDebug()<<sql;
        query.exec(sql);
        DataInfoBean datainfobean;
        while (query.next())
        {
//            datainfobean.UserID=query.value(1).toString();
//            datainfobean.UserName=query.value(2).toString();
//            datainfobean.UserAge=query.value(3).toString();
//            datainfobean.UserSex=query.value(4).toString();
//            datainfobean.UserHeight=query.value(5).toString();
//            datainfobean.UserWeight=query.value(6).toString();
//            datainfobean.MeasureTime=query.value(7).toString();
//            datainfobean.Path=query.value(8).toString();
            res.append(datainfobean);

        }
        return res;
    }
}
